package Day1.ZuoYe;

import Kit.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 *4:  添加如下的查询方法，包含姓名，qq号，手机号的模糊查找，以及分页
 * ，同时，要考虑如果某个条件为空（null或者空串），则忽略这个查询条件,
 * 方法声明如下:
 *   search(String sname,String qq,String telephone)
 */
public class FuzzySearch {
    public static void main(String[] args) {
        Scanner input=new Scanner(System.in);
        FuzzySearch fs = new FuzzySearch();
        Connection con = null;
        con = DBUtil.getConnection();
        System.out.println("请输入需要查询的姓名，qq号，手机号");
        String name = input.nextLine();
        String qq = input.nextLine();
        String tele = input.nextLine();
        System.out.println("请输入需要查询的页数和行数：");
        int ye = input.nextInt();
        int hang = input.nextInt();
        fs.search(con,name,qq,tele,ye,hang);
        DBUtil.close(con);
    }

    public void search(Connection con,String sname,String qq,String telephone,int ye,int hang){
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql ="select * from s_student where 1=1 ";
//        if(sname!=null&&sname.equals(" ")){
//            sql+="and sname like '%" + sname + "%' ";
//            list.add(sname);
//        }
//        if(qq!=null&&qq.equals(" ")){
//            sql+="and qq like '%" + qq + "%' ";
//            list.add(qq);
//        }
//        if(telephone!=null&&telephone.equals(" ")){
//            sql+="and telephone like '%" + telephone + "%' ";
//            list.add(telephone);
        if (sname != null && !sname.equals("")) {
            sql += "and sname like '%" + sname + "%' ";
        }
        if (qq != null && !qq.equals("")) {
            sql += "and qq like '%" + qq + "%' ";
        }
        if (telephone != null && !telephone.equals("")) {
            sql += "and telephone like '%" + telephone + "%' ";
        }
        sql+=" limit "+(ye-1)*hang+","+hang;
        System.out.println(sql);
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            for (int i = 0; i <hang&&rs.next() ; i++) {
                System.out.println(rs.getInt(1)+" "+rs.getString(2)
                        +" "+rs.getString(3)+" "+rs.getString(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(rs);
        }
    }
}
